
package com.mycompany.h2test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class h2test {
     static final String DRIVER_CLASS ="org.h2.Driver";//driver,warning:case sensitive 
       static final String JDBC_URL="jdbc:h2:tcp://localhost/D:/h2/b";
       static final String USER = "zyk";//username
       static final String PASSWORD = "zyk"; //password
       
       public static void main(String[] args) throws Exception {
        jdbcDemo();
        String a="201421101044";
        String b="123456";
        compl(a,b);
        String d="";
        show(d);
    }
       public static void jdbcDemo() throws Exception {
        Class.forName(DRIVER_CLASS);//load driver
        //On the base of URL user password connect SQL
        Connection conn=DriverManager.getConnection(JDBC_URL,USER,PASSWORD);
        Statement stmt = conn.createStatement();// createһ��Statement�
         stmt.execute("drop table if exists stu_manage");//if exist,delete
         stmt.execute("CREATE TABLE stu_manage(student_id VARCHAR(50),password VARCHAR(50))");
           stmt.executeUpdate("INSERT INTO stu_manage VALUES('201421101044','123456')");
           
       
        stmt.execute("drop table if exists stu_info");//if exist,delete
        stmt.execute("CREATE TABLE stu_info(id VARCHAR(36) PRIMARY KEY,name VARCHAR(100),sex VARCHAR(4),student_id VARCHAR(50),Escore VARCHAR(50),Mscore VARCHAR(50),Cscore VARCHAR(50))");
           stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','zyk','男','201421101044','80','85','90')");
           
    }
    static void p(String a){
        System.out.println(a);
    }//display 
    
    private static int num = 1;
    private static int getId() {
        return num++;
    }//autogenerate id
    
//    private static void show(Statement stmt) throws SQLException{
//     ResultSet rs = stmt.executeQuery("SELECT * FROM stu_info");
//        while (rs.next()) {
//          p(rs.getString(1) + "\t" + rs.getString("name") + "\t" + rs.getString("sex")+"\t"+rs.getString(4));
//        }
//    }
    
       public static int  compl(String a,String b) throws Exception {
        Class.forName(DRIVER_CLASS);//load driver
        //On the base of URL user password connect SQL
        Connection conn=DriverManager.getConnection(JDBC_URL,USER,PASSWORD);
        Statement stmt = conn.createStatement();// createһ��Statement�
       // stmt.executeUpdate("select id from stu_manage where name="+a);
        ResultSet rs = stmt.executeQuery("select * from stu_manage where name="+a);
        String str =rs.getString("student_id");
        if(str.equals(b)){
             return 1;
         }
         else 
         return 0;    
    }
        public static String  show(String a) throws Exception {
        Class.forName(DRIVER_CLASS);//load driver
        Connection conn=DriverManager.getConnection(JDBC_URL,USER,PASSWORD);
        Statement stmt = conn.createStatement();// createһ��Statement�  
        ResultSet rs = stmt.executeQuery("select * from stu_info where name="+a);
        
        String ss=rs.getString("student_id") + "\t" + rs.getString("name") + "\t" + rs.getString("sex")+"\t"+rs.getString("esocre")+"\t"+rs.getString("esocre")+"\t"+rs.getString("esocre");
 
        return  ss;  
    }
   
}